﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using System.IO;
namespace LanAnh
{
    public partial class frmConnectDatabase :Office2007Form
    {
        private string sourceConnectionString = "";
        public frmConnectDatabase()
        {
            InitializeComponent();
        }

        private void frmConnectDatabase_Load(object sender, EventArgs e)
        {
            gbUser.Enabled = false;
            btnTaoDatabase.Enabled = false;
//            this.LoadAllInstance();
        }
        private void LoadAllInstance()
        {
            DataTable dataSources = SqlDataSourceEnumerator.Instance.GetDataSources();
            this.DisplayData(dataSources);
        }
        private void DisplayData(DataTable table)
        {
            for (int i = 0; i < table.Rows.Count; i++)
            {
                this.cbSourceServer.Items.Add(table.Rows[i][0].ToString() + @"\" + table.Rows[i][1].ToString());
            }
        }
        private DataTable ConnectServer(string ServerName, string user, string pass, out string connectionString, bool bWindowsAuthentication)
        {
            DataTable table2;
            if (bWindowsAuthentication)
            {
                connectionString = "Data Source=" + ServerName + "; Integrated Security=True;";
            }
            else
            {
                connectionString = "Data Source=" + ServerName + "; Integrated Security=False; User id = " + user + ";Password=" + pass + ";";
            }
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                DataTable schema = connection.GetSchema("Databases");
                connection.Close();
                table2 = schema;
            }
            catch
            {
                table2 = null;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
            return table2;
        }
        private void btnSourceServerConnect_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable table;
                if (this.chkWindowsAuthentication.Checked)
                {
                    table = this.ConnectServer(this.cbSourceServer.Text, "", "", out this.sourceConnectionString, this.chkWindowsAuthentication.Checked);
                    if (table != null)
                    {
                        this.cbSourceDatabases.DataSource = table;
                        this.cbSourceDatabases.DisplayMember = "database_name";
                        this.cbSourceDatabases.ValueMember = "database_name";
                    }
                    else
                    {
                        this.cbSourceDatabases.DataSource = table;
                    }
                    btnTaoDatabase.Enabled = true;
                }
                else if ((this.txtSourceServerPass.Text.Replace(" ", "") != "") && (this.txtSourceServerUser.Text.Replace(" ", "") != ""))
                {
                    table = this.ConnectServer(this.cbSourceServer.Text, this.txtSourceServerUser.Text, this.txtSourceServerPass.Text, out this.sourceConnectionString, this.chkWindowsAuthentication.Checked);
                    if (table != null)
                    {
                        this.cbSourceDatabases.DataSource = table;
                        this.cbSourceDatabases.DisplayMember = "database_name";
                        this.cbSourceDatabases.ValueMember = "database_name";
                    }
                    else
                    {
                        this.cbSourceDatabases.DataSource = table;
                    }
                    btnTaoDatabase.Enabled = true;
                }
                else
                {
                    MessageBox.Show("User or Password incorrect");
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

        private void btnLoadSourceDatabase_Click(object sender, EventArgs e)
        {
            if (this.cbSourceDatabases.Items.Count > 0)
            {
                DataTable table = this.LoadTablesName(this.sourceConnectionString, this.cbSourceDatabases.SelectedValue.ToString());
                this.listSourceTables.Items.Clear();
                try
                {
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        this.listSourceTables.Items.Add(table.Rows[i][0].ToString());
                    }
                }
                catch { }
            }
        }
        private DataTable LoadTablesName(string connectionString, string databaseName)
        {
            string str = connectionString;
            str = str + "Initial Catalog=" + databaseName;
            DataTable table = new DataTable("Tables");
            try
            {
                using (SqlConnection connection = new SqlConnection(str))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.Tables where (TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' and table_name <> 'sysdiagrams')Order by Name";
                    connection.Open();
                    table.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                }
                return table;
            }
            catch
            {
                return null;
            }
        }

        private void buttonX2_Click(object sender, EventArgs e)
        {
            String sSQLNew = this.sourceConnectionString + "Initial Catalog=" + cbSourceDatabases.SelectedValue.ToString();
            if (Data.ChangeConnection(sSQLNew))
            {
                MessageBox.Show("Đã chuyển kết nối xong");
                MyServerConfig.SServerAdd = sSQLNew;
                MyServerConfig.SaveServerConfig();
            }
            else
            {
                MessageBox.Show("Chuyển kết nối thất bại");
            }
        }

        private void buttonX3_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void chkWindowsAuthentication_CheckedChanged(object sender, EventArgs e)
        {
            if (chkWindowsAuthentication.Checked == true)
            {
                gbUser.Enabled = true;
            }
            else
            {
                gbUser.Enabled = false;
            }
        }

        private void buttonX4_Click(object sender, EventArgs e)
        {
            LoadAllInstance();
        }

        private void btnTaoDatabase_Click(object sender, EventArgs e)
        {
            if (txtDBName.Text.Length > 0)
            {
                String sSQL = "CREATE DATABASE " + txtDBName.Text;
                using (SqlConnection connection = new SqlConnection(sourceConnectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = sSQL;
                    connection.Open();
                    try
                    {
                        command.ExecuteNonQuery();
                        SqlCommand __cmd;
                        StreamReader __streamReader;
                        string script;
                        string[] cmds;
                        if (connection.State == ConnectionState.Open)
                            connection.Close();
                        connection.ConnectionString = connection.ConnectionString + "Initial Catalog=" + txtDBName.Text;
                        connection.Open();
                        __cmd = connection.CreateCommand();
                        __streamReader = new StreamReader("script.sql");
                        script = __streamReader.ReadToEnd();
                        cmds = script.Replace("GO", "~").Split(new Char[] { '~' });
                        foreach (string cmd in cmds)
                        {
                            __cmd.CommandType = CommandType.Text;
                            __cmd.CommandText = cmd;
                            int i = __cmd.ExecuteNonQuery();
                        }
                        string insertCV = "INSERT INTO tblDangNhap VALUES (N'Admin',N'202cb962ac59075b964b07152d234b70',N'Toàn Quyền',NULL,N'Administrator')";
                        SqlCommand cmd1 = new SqlCommand(insertCV, connection);
                        int k1 = cmd1.ExecuteNonQuery();
                    }
                    catch(Exception ex)
                    {
                        MessageBox.Show("Lỗi không thể tạo database: " + ex.Message);
                    }
                }
            }
        }
    }
}
